﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using AtletIS.Shared.AtletisService;
using AtletIS.Shared.Data.aDbDataSetTableAdapters;
using AtletIS.Shared.Data;
using System.Data.SqlClient;

namespace AtletIS.Shared.Helpers
{
    public static class DownloadHelper
    {
        public static void DownloadFromWcf(SyncParameters syncParams)
        {
            int error = 0;
            string error_text = string.Empty;
            
            DataTable dt;
            using (AtletisServiceClient client = new AtletisServiceClient())
            {
                dt = client.GetTable(syncParams, ref error, ref error_text);
            }

            if (dt != null && dt.Rows.Count > 0)
            {

                #region Get local table

                DataTable dtLocal = new DataTable();
                switch (syncParams.ActiveTable)
                {
                    #region Tables

                    case Tables.Categories:
                        dtLocal = new CategoriesTableAdapter().GetData();
                        break;

                    case Tables.DisciplinesGroups:
                        dtLocal = new DisciplinesGroupsTableAdapter().GetData();
                        break;

                    case Tables.Disciplines:
                        dtLocal = new DisciplinesTableAdapter().GetData();
                        break;

                    case Tables.Countries:
                        dtLocal = new CountriesTableAdapter().GetData();
                        break;

                    case Tables.ClubsGroups:
                        dtLocal = new ClubsGroupsTableAdapter().GetData();
                        break;

                    case Tables.Clubs:
                        dtLocal = new ClubsTableAdapter().GetData();
                        break;

                    case Tables.Competitors:
                        dtLocal = new CompetitorsTableAdapter().GetData();
                        break;

                    case Tables.ChipTagsGroups:
                        dtLocal = new ChipTagsGroupsTableAdapter().GetData();
                        break;

                    #endregion

                    #region Competition

                    case Tables.CompetitionsById:
                        dtLocal = new CompetitionsTableAdapter().GetData();
                        break;

                    case Tables.DisciplineListForCompetition:
                        dtLocal = new DisciplineListTableAdapter().GetData();
                        break;

                    case Tables.ExtTextsForDisciplineList:
                    case Tables.ExtTextsForPerformance:
                        dtLocal = new ExtTextsTableAdapter().GetData();
                        break;

                    case Tables.CheckPoints:
                        dtLocal = new CheckPointsTableAdapter().GetData();
                        break;

                    case Tables.Performances:
                        dtLocal = new PerformancesTableAdapter().GetData();
                        break;

                    #endregion

                    default:
                        break;
                }

                #endregion

                #region Change [ID] <-> [WebId]

                foreach (DataRow dr in dt.Rows)
                {
                    if (dt.Columns.Contains("WebId"))
                    {
                        dr["WebId"] = dr["ID"];

                        string filter = "[WebId] = " + dr["WebId"];
                        if (dtLocal.Select(filter).Count() == 0)
                        {
                            dr["ID"] = 0;
                        }
                        else
                        {
                            dr["ID"] = dtLocal.Select(filter)[0]["ID"];
                        }
                    }

                    if (dt.Columns.Contains("Sync"))
                    {
                        dr["Sync"] = 1;
                    }

                    if (dt.Columns.Contains("Modified"))
                    {
                        dr["Modified"] = DateTime.Now;
                    }
                }

                #endregion

                #region ChipTags FKs

                if (dt.Columns.Contains("ChipTagsGroupId"))
                {
                    DataTable dtChipTagsGroups = new ChipTagsGroupsTableAdapter().GetData();

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        string filter = "WebId = " + syncRow["ChipTagsGroupId"];
                        syncRow["ChipTagsGroupId"] = dtChipTagsGroups.Select(filter)[0]["ID"];
                    }
                }

                #endregion

                #region DisciplineList FKs

                // DisciplineList.CompetitionId
                if (dt.Columns.Contains("CompetitionId"))
                {
                    DataTable dtCompetitions = new CompetitionsTableAdapter().GetData();

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        string filter = "WebId = " + syncRow["CompetitionId"];
                        syncRow["CompetitionId"] = dtCompetitions.Select(filter)[0]["ID"];
                    }
                }

                // DisciplineList.ParentId
                if (dt.Columns.Contains("ParentId"))
                {
                    DataTable dtDisciplineList = DatabaseHelper.GetAllDisciplineListByCompetitionIdAll(Globals.ActiveCompetition.ID);

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        if (Convert.ToInt32(syncRow["ParentId"]) != 0)
                        {
                            string filter = "WebId = " + syncRow["ParentId"];
                            syncRow["ParentId"] = dtDisciplineList.Select(filter)[0]["ID"];
                        }
                    }
                }

                // DisciplineList.CategoryId
                if (dt.Columns.Contains("CategoryId"))
                {
                    DataTable dtCategories = new CategoriesTableAdapter().GetData();

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        string filter = "WebId = " + syncRow["CategoryId"];
                        syncRow["CategoryId"] = dtCategories.Select(filter)[0]["ID"];
                    }
                }

                // DisciplineList.DisciplineId
                if (dt.Columns.Contains("DisciplineId"))
                {
                    DataTable dtDisciplines = new DisciplinesTableAdapter().GetData();

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        string filter = "WebId = " + syncRow["DisciplineId"];
                        syncRow["DisciplineId"] = dtDisciplines.Select(filter)[0]["ID"];
                    }
                }

                #endregion

                #region Other FKs

                // [Performances].[DisciplineListId] / [CheckPoints].[DisciplineListId] / [ExtTexts].[DisciplineListId]
                if (dt.Columns.Contains("DisciplineListId"))
                {
                    DataTable dtDisciplineList = DatabaseHelper.GetAllDisciplineListByCompetitionIdAll(Globals.ActiveCompetition.ID);

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        if (syncRow["DisciplineListId"] != DBNull.Value)
                        {
                            string filter = "WebId = " + syncRow["DisciplineListId"];
                            syncRow["DisciplineListId"] = dtDisciplineList.Select(filter)[0]["ID"];
                        }
                    }
                }

                // [Performances].[CompetitorId]
                if (dt.Columns.Contains("CompetitorId"))
                {
                    DataTable dtCompetitors = new CompetitorsTableAdapter().GetData();

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        string filter = "WebId = " + syncRow["CompetitorId"];
                        syncRow["CompetitorId"] = dtCompetitors.Select(filter)[0]["ID"];
                    }
                }

                // [ExtTexts].[PerformanceId]
                if (dt.Columns.Contains("PerformanceId"))
                {
                    DataTable dtPerformances = DatabaseHelper.GetPerformancesByCompetitionId(Globals.ActiveCompetition.ID);

                    foreach (DataRow syncRow in dt.Rows)
                    {
                        if (syncRow["PerformanceId"] != DBNull.Value)
                        {
                            string filter = "WebId = " + syncRow["PerformanceId"];
                            syncRow["PerformanceId"] = dtPerformances.Select(filter)[0]["ID"];
                        }
                    }
                }

                #endregion

                dt.AcceptChanges();

                if (dt.Columns.Contains("ChipTagsGroupId") & dt.Columns.Contains("ChipTag"))    // ChipTags
                {
                    DataTable dtChipTagsLocal = new ChipTagsTableAdapter().GetData();
                    foreach (DataRow dr in dt.Rows)
                    {
                        string filter = "[ChipTagsGroupId] = " + dr["ChipTagsGroupId"].ToString() + " AND [ChipTag] = '" + dr["ChipTag"].ToString() + "'";
                        if (dtChipTagsLocal.Select(filter).Count() == 0)
                        {
                            dr.SetAdded();
                        }
                        else
                        {
                            dr.SetModified();
                        }
                    }

                }
                else if (dt.Columns.Contains("WebId"))  // Sync via [WebId]
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (Convert.ToInt32(dr["ID"]) == 0)
                        {
                            dr.SetAdded();
                        }
                        else
                        {
                            dr.SetModified();
                        }
                    }
                }
                else    // Sync via [ID]
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        string filter = "[ID] = '" + dr["ID"] + "'";
                        if (dtLocal.Select(filter).Count() == 0)
                        {
                            dr.SetAdded();
                        }
                        else
                        {
                            dr.SetModified();
                        }
                    }
                }

                // Save changes
                string sql = string.Empty;
                SqlCommand cmd = new SqlCommand();
                switch (syncParams.ActiveTable)
                {
                    #region Tables

                    case Tables.Categories:
                        sql = "SELECT * FROM Categories";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.DisciplinesGroups:
                        sql = "SELECT * FROM DisciplinesGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Disciplines:
                        sql = "SELECT * FROM Disciplines";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Countries:
                        sql = "SELECT * FROM Countries";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ClubsGroups:
                        sql = "SELECT * FROM ClubsGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Clubs:
                        sql = "SELECT * FROM Clubs";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Competitors:
                        sql = "SELECT * FROM Competitors";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ChipTagsGroups:
                        sql = "SELECT * FROM ChipTagsGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ChipTags:
                        sql = "SELECT * FROM ChipTags";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    #region Competition

                    case Tables.CompetitionsById:
                        sql = "SELECT * FROM Competitions";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.DisciplineListForCompetition:
                        sql = "SELECT * FROM DisciplineList";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ExtTextsForDisciplineList:
                    case Tables.ExtTextsForPerformance:
                        sql = "SELECT * FROM ExtTexts";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.CheckPoints:
                        sql = "SELECT * FROM CheckPoints";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Performances:
                        sql = "SELECT * FROM Performances";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    default:
                        return;
                }

                cmd.Connection = new SqlConnection(Properties.Settings.Default.atletisConnectionString);
                using (var adapter = new SqlDataAdapter(cmd))
                {
                    using (var builder = new SqlCommandBuilder(adapter))
                    {
                        builder.ConflictOption = ConflictOption.OverwriteChanges;
                        builder.SetAllValues = true;
                        adapter.Update(dt);
                    }
                }
            }
        } // DownloadFromWcf





    }
}
